<?php
/** search dvd Model class
*
* This is searching dvd class model to search for a movies or games depend on the input from the customer
*
* @author  Zoms
* @version 39
* @copyright copyright (c) 2011, Boston Univeristy Under BSD
* 
*/

class Model_reserve extends CI_Model {
    
    function search_reservations()
    {
            // The DB query to search for the movies 
            $query_str = "SELECT reserv_id, reserv_date , reserv_time_start, 
                reserv_time_end FROM `T_SPE_RESERV` WHERE has_been_resrved_flag = 0 and
                finished_date is null";
            
            // run the query with the required search input to get the results with movies titles that contains part of the search input
            $search_results = $this->db->query($query_str);
            
            // check if the result contains more than one row of the DB query it will return array of the movies contains the search input  or  if less than or equal will return FALSE
            if($search_results->num_rows() > 0)
            {
                return $search_results;
               
            }
            else
            {
                return false;
            }
       
    }
    function check_block($cus_id)
    {
        $query_str = "SELECT reserv_id , black_listed_date
                FROM `T_SPE_RESERV_HIS`  WHERE 
                cus_id = ? and black_listed_date is not null";
             //run the query with the customer id
            $result = $this->db->query($query_str ,$cus_id);
        if ($result->num_rows() > 0)
            {
              return true;
            }
            else
            {
                return false;
            }
    }
    function reserve_place($reserve_id , $cus_id , $spcialevent)
    {
        $query_str = "SELECT store_loc_id
                FROM `T_CUSTOMERS` C , `T_DELIVERY_LOCATION` DL WHERE 
                store_c_id = c_id and cus_id = ? ";
             //run the query with the customer id
            $result = $this->db->query($query_str ,$cus_id);
                
            if($result->num_rows() > 0)
            {           
                $cus_loc = $result->row(0)->store_loc_id;
            }
            else
            {
                $cus_loc = 1; // this is the master location
            }
            
            
            $query_str = "SELECT spe_id
                FROM `T_SPECIALS` S  WHERE 
                spe_type = ? ";
             //run the query with the customer id
            $result = $this->db->query($query_str ,$spcialevent);
                
            if($result->num_rows() > 0)
            {           
                $spe_id = $result->row(0)->spe_id;

                $query_str = "UPDATE `T_SPE_RESERV` SET cus_id= ? , spe_id = ?, store_loc= ?
                    , has_been_resrved_flag = 1 WHERE reserv_id = ?";

                // run the query with the required search input to get the results with movies titles that contains part of the search input
                $result2 = $this->db->query($query_str, array($cus_id, $spe_id, $cus_loc, $reserve_id));

                if($result2)
                {           
                    return true;
                }
                else
                {
                    return false;
                }
             }
             else
             {
                $spe_id = null; // this is the master location
                return false;
             }
    }
    
    function get_all_special()
    {
        $query_str = "SELECT spe_id , spe_type, spe_desc , spe_price , available_flag 
            FROM `t_specials`
            ORDER BY spe_id ASC";
        // run the query to get the results with posts titles
        $query = $this->db->query($query_str);

        if ($query->num_rows() > 0) {
            return $query->result_array();//result();
        } else {
            return array();
        }
    }   
    
    function  get_my_reservations($cus_id)
    {
        $query_str = "SELECT SR.spe_id , S.spe_type , S.spe_desc , S.spe_price, 
            SR.store_loc, S.available_flag , SR.special_desc , SR.cus_id ,
            SR.reserv_id, SR.reserv_date , SR.reserv_time_start, SR.reserv_time_end 
            FROM `T_SPE_RESERV` SR , `T_SPECIALS` S WHERE S.spe_id = SR.spe_id and 
            S.available_flag != 0 and cus_id = ? and has_been_resrved_flag = 1
            and finished_date is null ORDER BY spe_id ASC";
        
            // run the query to get the results with posts titles
            $query = $this->db->query($query_str, $cus_id);
            
              if ($query->num_rows() > 0)
                {
                    return $query->result();
                  
                }
                else
                {
                    return array();
                    //false;
                }
        
    }  
    
    function  cancel_reservation($reserve_id)
    {
        $query_str = "UPDATE `T_SPE_RESERV` SET 
            cus_id = null , spe_id = null , store_loc = null , has_been_resrved_flag = 0 
                WHERE reserv_id = ?";
        
            // run the query to get the results with posts titles
            $query = $this->db->query($query_str, $reserve_id);
            
              if ($query)
                {
                    return true;
                  
                }
                else
                {
                    return false;
                }
        
    }  
    
    function  paid_reservation($cus_id , $res_queue) // update and insert into history
    {
            $query_str = "SELECT cus_email , cus_pay_id FROM `T_CUSTOMERS` where 
             cus_id = ?";
           
            $result = $this->db->query($query_str, $cus_id);
            
            $cus_email = $result->row(0)->cus_email;
            $cus_pay_id = $result->row(0)->cus_pay_id;
            $query_str = "UPDATE `T_SPE_RESERV` SET 
            finished_date =  now() WHERE cus_id = ?";
            // run the query to get the results with posts titles
            $query = $this->db->query($query_str, $cus_id);
            
              if ($query && $result)
                {
                  foreach ($res_queue['reserv'] as $item) 
                      { 
                      $query_str2 = "INSERT INTO `T_SPE_RESERV_HIS` (reserv_id , cus_id, 
                          spe_id , store_loc , special_desc , reserv_date, reserv_time_start,
                          reserv_time_end , libility_amt ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)";

                       $query2 = $this->db->query($query_str2, array($item->reserv_id,
                           $item->cus_id,$item->spe_id,$item->store_loc,$item->special_desc,
                               $item->reserv_date,$item->reserv_time_start,$item->reserv_time_end,
                           $item->spe_price));
                       
                        //$query_str3 = "update `T_CUS_PAYMENT` SET dues = dues + ? WHERE payment_id = ? ";

                        //$query3 = $this->db->query($query_str3, array($item->spe_price , $cus_pay_id));
                       
                       
                        }
                    if ($query2)// && $query3)
                    {
                        $this->email->from('yogettenet@gmail.com', 'YOGETTE\'s Team');
                        $this->email->to($cus_email); 
                        $this->email->cc('zs.master@hotmail.com'); 
                        $this->email->subject('Reservaion Confirmation from YOGETTE');
                        $this->email->message('Congratulation You Have successfully completed you reservation. Thank you For choosing Yoggete.');	
                        $this->email->send();
                        return true;
                    }
                    else
                    {
                        return false;
                    } 
                }
                else
                {
                    return false;
                }
        
    }  
    
    
    function add_reserve($specialdesc, $special_date, $reserv_time_start, $reserv_time_end , $emp_email)
    {
        $query_str = "INSERT INTO `T_SPE_RESERV` ( special_desc , reserv_date, reserv_time_start,
                          reserv_time_end ) VALUES (?, ?, CONVERT(?, TIME), CONVERT(?, TIME))";

           $query = $this->db->query($query_str, array($specialdesc,$special_date,
               $reserv_time_start,$reserv_time_end));

        if ($query)
        {
            $this->email->from('yogettenet@gmail.com', 'YOGETTE\'s Team');
            $this->email->to($emp_email); 
            $this->email->cc('zs.master@hotmail.com'); 
            $this->email->subject('Add New Reservaion Confirmation from YOGETTE');
            $this->email->message('Congratulation You Have successfully Added a Reservation Schedule. Thank you.');	
            $this->email->send();
            return true;
        }
        else
        {
            return false;
        } 
    }
    
    function check_exist_reservation($special_date , $reserv_time_start , $reserv_time_end)
    {
        $query_str = "SELECT reserv_id FROM `T_SPE_RESERV` WHERE reserv_date = CONVERT(?, DATE) 
            AND reserv_time_start = CONVERT(?, TIME)
            AND reserv_time_end = CONVERT(?, TIME) and has_been_resrved_flag = 0";

           $query = $this->db->query($query_str, array($special_date,
               $reserv_time_start,$reserv_time_end));

        if ($query->num_rows() > 0)
        {
            return true;
        }
        else
        {
          
            $query_str = "SELECT reserv_id , reserv_date ,reserv_time_start, reserv_time_end 
                FROM `T_SPE_RESERV` WHERE reserv_date = CONVERT(?, DATE)";

            $query = $this->db->query($query_str, array($special_date));
            $result = false;
            if ($query->num_rows() > 0)
            {
                // Counter
                $counter = 0;
                
                $startDate = explode(":", $reserv_time_start);
                //echo $startDate[0]; 

                $endDate = explode(":", $reserv_time_end);
                //echo $endDate[0];     
                
                 foreach ($query->result_array() as $row) 
                  {
                     
                    //echo "hello";
                     if ($special_date == $row['reserv_date'])
                       {

                        $storedstartDate = explode(":", $row['reserv_time_start']);
                            
                        $storedendDate = explode(":", $row['reserv_time_end']);
                        
                         //echo $storedstartDate[0]; 
                         //echo $storedendDate[0];
                
                         if ($endDate[0] < $storedstartDate[0] )
                         {
                              $result =  false;
                         }
                         else if ($startDate[0] > $storedendDate[0])
                         {
                            $result =  false;
                         }
                         else 
                         {
                             $result =  true;
                            
                             break;
                         }

                     }
                 }
            }
            return $result;
        } 
    }
   
}


?>